/****** Object:  StoredProcedure [dbo].[sp_po_futures_order_set]    Script Date: 11/11/2009 13:20:22 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_po_futures_order_set]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_po_futures_order_set]
GO
/****** Object:  StoredProcedure [dbo].[sp_po_futures_order_set]    Script Date: 11/11/2009 13:18:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************************************
* sp Name: dbo.sp_po_futures_order_set
* Created: 20100804, RGM
* Purpose: establece el orden consecutivo (sin duplicados ni huecos) del
*	campo futures_order para las po de version de un periiodo de futuros
*	en especifico
*
* Modified: 20100820 RGM, se quita el filtro de tipo de maiz para que ordene
*		todos los elementos
*
* HistoryDate.
* yyyymmdd User, data
* 
* Note: nothing
* 
***********************************************************************/
CREATE PROCEDURE [dbo].[sp_po_futures_order_set]
	@futurePeriodVersionId	INT, 
	@DEBUG					INT = 0
AS

/*
	error handling
*/
BEGIN TRY
	DECLARE
		@TranStarted BIT
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 ) BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END ELSE BEGIN
		SET @TranStarted = 0
	END

	SET NOCOUNT ON
-------------------------------------------------------
--[BEGIN] SP section
DECLARE @text VARCHAR(512)

SET @text = '	------ Ejecutando sp_po_futures_order_set ' 
		+ CONVERT(VARCHAR(10), @futurePeriodVersionId)

IF @DEBUG = 1
	PRINT @text

DECLARE 
	@purchaseOrderVersionId	INT, 
	@newOrder				INT, 

-- manejo de entorno
	@RCOUNT					INT

--tabla temporal para ordenamiento
CREATE TABLE #po_order (
	futures_order				INT, 
	futures_new_order			INT NOT NULL DEFAULT 0, 
	biweek_period_version_id	INT, 
	priority					INT, 
	code						VARCHAR(10), 
	purchase_order_version_id	INT
)

-- insercion de las oc segun ordenamiento actual
INSERT INTO #po_order (futures_order, biweek_period_version_id, priority, code, purchase_order_version_id)
	SELECT pov.futures_order, pov.biweek_period_version_id, t.priority, 
			RTRIM(pov.code) + dbo.fn_zero_padding(pov.code_number, 3) AS code, 
			pov.purchase_order_version_id
		FROM purchases_orders_versions pov, vw_periods_versions pv, transports t, 
			corns_types ct
		WHERE pov.biweek_period_version_id = pv.biweek_period_version_id
			AND pov.transport_id = t.transport_id
			AND pov.corn_type_id = ct.corn_type_id
			AND pov.is_transit = 0
--			AND ct.corn_origin_id = 'I'
			AND pov.futures_order < 999			-- para evitar ponerle nuevo valor a los que "no tienen"
			AND pv.future_period_version_id = @futurePeriodVersionId
		ORDER BY pov.futures_order, pov.biweek_period_version_id, 
			t.priority DESC, pov.code, pov.code_number

SET @RCOUNT = @@ROWCOUNT
IF @DEBUG = 1
	PRINT 'I:#po_order [' + CONVERT(VARCHAR(10), @RCOUNT) + ']'


-- creacion del cursor para reasingacion del orden
SET @newOrder = 0

DECLARE newOrder CURSOR FOR
SELECT purchase_order_version_id
	FROM #po_order

OPEN newOrder

FETCH NEXT FROM newOrder INTO @purchaseOrderVersionId

-- ciclo de reordenamiento
WHILE @@FETCH_STATUS = 0 BEGIN
	SET @newOrder = @newOrder + 1
	
	UPDATE #po_order SET 
			futures_new_order = @newOrder
		WHERE purchase_order_version_id = @purchaseOrderVersionId

/*
	SET @RCOUNT = @@ROWCOUNT
	IF @DEBUG = 1 BEGIN
		PRINT 'U:#po_order [' + CONVERT(VARCHAR(10), @RCOUNT) + '] ' + 
					'purchase_order_version_id[' + CONVERT(VARCHAR(10), @purchaseOrderVersionId) + '] ' + 
					'futures_new_order[' + CONVERT(VARCHAR(10), @newOrder) + ']'
	END
*/

	FETCH NEXT FROM newOrder INTO @purchaseOrderVersionId
END

CLOSE newOrder
DEALLOCATE newOrder

UPDATE p SET 
		p.futures_order = po.futures_new_order
	FROM purchases_orders_versions p, #po_order po
	WHERE p.purchase_order_version_id = po.purchase_order_version_id

SET @RCOUNT = @@ROWCOUNT
IF @DEBUG = 1
	PRINT 'U:purchases_orders_versions [' + CONVERT(VARCHAR(10), @RCOUNT) + ']'


IF @DEBUG = 1
	PRINT 'END:' + @text

--[END] SP section
-------------------------------------------------------
	SET NOCOUNT OFF

	IF( @TranStarted = 1 ) BEGIN
		SET @TranStarted = 0
		COMMIT TRANSACTION
	END

	RETURN 1 --simil boleano a true
END TRY BEGIN CATCH
	DECLARE 
		@ErrorMessage	NVARCHAR(4000), 
		@ErrorNumber	INT, 
		@ErrorSeverity	INT, 
		@ErrorState		INT

	SELECT @ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorNumber = ERROR_NUMBER()

	IF( @TranStarted = 1 ) BEGIN
		SET @TranStarted = 0
		ROLLBACK TRANSACTION
	END

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
	RETURN 0 --simil boleano a mal
END CATCH 



